import pymysql
import pymysql.cursors

# 连接数据库
connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    db='python',
    charset='utf8'
)

# 获取游标
cursor = connect.cursor()

# 插入数据
sql = "INSERT INTO stu (name, profession, age, hometown) VALUES ( '%s', '%s', %d, '%s' )"
data = ('lironghao', 'Music', 24, 'XianNingHuBei')
cursor.execute(sql % data)
connect.commit()
print('成功插入', cursor.rowcount, '条数据')

# 修改数据
sql = "UPDATE stu SET age = %d WHERE name = '%s' "
data = (21, 'lironghao')
cursor.execute(sql % data)
connect.commit()
print('成功修改', cursor.rowcount, '条数据')

# 查询数据
sql = "SELECT profession, hometown FROM stu WHERE name = '%s' "
data = ('xiaozhan',)
cursor.execute(sql % data)
for row in cursor.fetchall():
    print("profession:%s\thometown:%s" % row)
print('共查找出', cursor.rowcount, '条数据')

# 删除数据
sql = "DELETE FROM stu WHERE name = '%s' LIMIT %d"
# 检索前 7 个记录行
data = ('lironghao', 7)
cursor.execute(sql % data)
connect.commit()
print('成功删除', cursor.rowcount, '条数据')


# 关闭连接
cursor.close()
connect.close()